package per.lee.spingboothelper.common.export;



import java.awt.Color;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;

import per.lee.spingboothelper.common.export.bean.CellMulti;
import per.lee.spingboothelper.common.export.bean.SpanTitle;
import sun.misc.BASE64Encoder;

/**
 * 导出excel工具
 * @author lixiaoying
 */
public class ExportBaseUtils {


    public static void exportInit(HttpServletRequest request, HttpServletResponse response, String fileName) throws IOException {
        fileName = ExportBaseUtils.encodeFilename(fileName, request);
        //response.setCharacterEncoding("UTF-8");
        // 告诉浏览器用什么软件可以打开此文件
        response.setContentType("application/octet-stream");
        // 下载文件的默认名称
        //response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
    }

    /**
     * 创建标题Font
     *
     * @param wb
     * @return
     */
    public static Font createTitleFont(XSSFWorkbook wb) {
        Font titleFont = wb.createFont();
        titleFont.setFontName("SimSum");
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(IndexedColors.BLACK.index);
        return titleFont;
    }

    /**
     * 创建标题单元格样式
     *
     * @param wb
     * @param titleFont
     * @return
     */
    public static XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb, Font titleFont) {
        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        return titleStyle;
    }

    /**
     * 创建标题Font
     *
     * @param wb
     * @return
     */
    public static Font createDataFont(XSSFWorkbook wb) {
        Font dataFont = wb.createFont();
        dataFont.setFontName("华文宋体");
        dataFont.setFontHeightInPoints((short) 12);
        dataFont.setColor(IndexedColors.BLACK.index);
        return dataFont;
    }

    /**
     * 创建标题单元格样式
     *
     * @param wb
     * @param dataFont
     * @return
     */
    public static XSSFCellStyle createDataCellStyle(XSSFWorkbook wb, Font dataFont) {

        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setFont(dataFont);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        return dataStyle;
    }

    public static int writeSpanTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<SpanTitle> titles) {
        int rowIndex = 0;

        Font titleFont = ExportBaseUtils.createTitleFont(wb);
        XSSFCellStyle titleStyle = ExportBaseUtils.createTitleCellStyle(wb, titleFont);

        ExportBaseUtils.setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        //解析获得表头的行
        int rowNum = titles.stream().max((t1, t2) -> t1.getLastRow().compareTo(t2.getLastRow())).get().getLastRow();
        Map<Integer, Row> rowMap = new HashMap<>();
        //预创建row
        for (int i = 0; i <= rowNum; i++) {
            rowMap.put(i, sheet.createRow(i));
        }
        for (int i = 0; i < titles.size(); i++) {
            SpanTitle spanTitle = titles.get(i);
            Cell cell = rowMap.get(spanTitle.getFirstRow()).createCell(spanTitle.getFirstCol());
            if (!(spanTitle.getFirstRow().equals(spanTitle.getLastRow()) && spanTitle.getFirstCol().equals(spanTitle.getLastCol()))) {
                try{

                    sheet.addMergedRegion(new CellRangeAddress(spanTitle.getFirstRow(), spanTitle.getLastRow(), spanTitle.getFirstCol(), spanTitle.getLastCol()));
                }catch (Exception e){
                    System.out.println("sheet = "+sheet.getSheetName()+" title="+spanTitle.getTitle());
                }

            }
            cell.setCellValue(spanTitle.getTitle());
            cell.setCellStyle(titleStyle);

            int length = spanTitle.getTitle().getBytes().length;
            sheet.setColumnWidth(i,length*256);
        }

        rowIndex += rowMap.size();
        return rowIndex;
    }

    /**
     * 普通标题写入
     *
     * @param wb
     * @param sheet
     * @param titles
     * @return
     */
    public static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
        int rowIndex = 0;
        int colIndex = 0;

        Font titleFont = wb.createFont();
        titleFont.setFontName("SimSum");
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        Row titleRow = sheet.createRow(rowIndex);
        // titleRow.setHeightInPoints(25);
        colIndex = 0;

        for (int j = 0; j < titles.size(); j++) {
        	String field  = titles.get(j);
        	Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
            
           
		}
       

        rowIndex++;
        return rowIndex;
    }

    /**
     * 复杂cell写入
     *
     * @param wb
     * @param sheet
     * @param rows
     * @param rowIndex
     * @return
     */
    public static int writeMultiRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<CellMulti>> rows, int rowIndex) {
        int colIndex = 0;
        Font dataFont = createDataFont(wb);
        XSSFCellStyle dataStyle = createDataCellStyle(wb, dataFont);

        for (List<CellMulti> rowData : rows) {
            Row dataRow = sheet.createRow(rowIndex);
            colIndex = 0;

            for (CellMulti cellData : rowData) {
                Cell cell = dataRow.createCell(colIndex);
                if (cellData != null) {
                    cell.setCellValue(cellData.getValue().toString());
                } else {
                    cell.setCellValue("");
                }

                cell.setCellStyle(dataStyle);
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    /**
     * 简单数据写入
     *
     * @param wb
     * @param sheet
     * @param rows
     * @param rowIndex
     * @return
     */
    public static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
        int colIndex = 0;

        Font dataFont = createDataFont(wb);
        XSSFCellStyle dataStyle = createDataCellStyle(wb, dataFont);

        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        for (List<Object> rowData : rows) {
            Row dataRow = sheet.createRow(rowIndex);
            // dataRow.setHeightInPoints(25);
            colIndex = 0;
            
            for (Object cellData : rowData) {
                Cell cell = dataRow.createCell(colIndex);
                if (cellData != null) {
                    cell.setCellValue(cellData.toString());
                    
                    int length = cellData.toString().getBytes().length;
                    sheet.setColumnWidth(colIndex,length * 150);
                    
                } else {
                    cell.setCellValue("");
                }

                cell.setCellStyle(dataStyle);
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    /**
     * 根据内容自动调整列宽
     *
     * @param sheet
     * @param columnNumber
     */
    public static void autoSizeColumns(Sheet sheet, int columnNumber) {

        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = (int) (sheet.getColumnWidth(i) + 100);
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    public static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(BorderSide.TOP, color);
        style.setBorderColor(BorderSide.LEFT, color);
        style.setBorderColor(BorderSide.RIGHT, color);
        style.setBorderColor(BorderSide.BOTTOM, color);
    }

    public static String encodeFilename(String filename, HttpServletRequest request) {
        /**
         * 获取客户端浏览器和操作系统信息 在IE浏览器中得到的是：User-Agent=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Maxthon; Alexa Toolbar)
         * 在Firefox中得到的是：User-Agent=Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.7.10) Gecko/20050717 Firefox/1.0.6
         */
        try {
            String agent = request.getHeader("USER-AGENT");
            if (agent.contains("Firefox")) { // 火狐浏览器
                filename = "=?UTF-8?B?"
                        + new BASE64Encoder().encode(filename.getBytes("utf-8"))
                        + "?=";
                filename = filename.replaceAll("\r\n", "");
            } else { // IE及其他浏览器
                filename = URLEncoder.encode(filename, "utf-8");
                filename = filename.replace("+", " ");
            }
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return filename;
    }
}

